package com.pic.mgt.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.pic.mgt.dao.syushin.ISyushinBusinessApDao;
import com.pic.mgt.dao.syushin.SyushinBusinessApDao;
import com.pic.mgt.manager.business.ApInfoBean;
import com.pic.mgt.manager.business.BasicBusinessInfoBean;
import com.pic.mgt.manager.business.LatestBucketInfoBean;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;

public final class BusinessListMgtDao extends BasicDao implements IBusinessListMgtDao {
	private final String tableName = "business_list_mgt";
	
	private final String insertBasicSqlScript;
	private final String findAllBusinessBasicSqlScript;
	private final String findBusinessBasicByBusinessNameSqlScript;
	private final String findLatestBucketInfoByBusinessNameSqlScript;
	private final String deleteByBusinessNameSqlScript;
	private final String updateLatestBasicBucketInfoByBusinessNameSqlScript;
	private final String updateLatestBucketStatusByBusinessNameSqlScript;
	
	public BusinessListMgtDao() {
		super();
		
		// 建立 Insert Basic 的 Sql Scritp
		this.insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 find business basic by business name 的 Sql Script
		this.findBusinessBasicByBusinessNameSqlScript = createFindBusinessBasicByBusinessNameSqlScript();
		
		// 建立 find basic latest bucket info bean by business name 的 Sql Script
		this.findLatestBucketInfoByBusinessNameSqlScript = createFindLatestBucketInfoByBusinessNameSqlScript();
		
		// 建立 find all business basic 的 Sql Scritp
		findAllBusinessBasicSqlScript = createFindAllBusinessBasicSqlScript();
		
		// 建立 delete by business name 的 Sql Scritp
		deleteByBusinessNameSqlScript = createDeleteByBusinessNameSqlScript();
		
		// 建立 Update latest basic bucket info by business name 的 Sql Script
		updateLatestBasicBucketInfoByBusinessNameSqlScript = 
			createUpdateLatestBasicBucketInfoByBusinessNameSqlScript();
		
		// 建立 Update latest bucket status by business name 的 Sql Script
		updateLatestBucketStatusByBusinessNameSqlScript = 
			createUpdateLatestBucketStatusByBusinessNameSqlScript();
	}
	
	private String createUpdateLatestBucketStatusByBusinessNameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_latestBucketStatus);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_businessName);
	}
	
	private String createUpdateLatestBasicBucketInfoByBusinessNameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_latestBucketNo);
		updateFieldsVector.add(_latestBucketStatus);
		updateFieldsVector.add(_latestBucketTime);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_businessName);
	}
	
	private String createInsertBasicSQLScript() {
		Vector<String> insertFieldsVector =
								new Vector<String>();
		
		insertFieldsVector.add(_businessName);
		insertFieldsVector.add(_businessType);
		
		return
			SqlScriptUtils.createNamedParamInsertScript(
										tableName,
										insertFieldsVector);
	}
	
	private String createFindLatestBucketInfoByBusinessNameSqlScript() {
		Vector<String> selectFieldsVector =
									new Vector<String>();

		selectFieldsVector.add(_businessName);
		selectFieldsVector.add(_businessType);
		selectFieldsVector.add(_latestBucketNo);
		selectFieldsVector.add(_latestBucketStatus);
		selectFieldsVector.add(_latestBucketTime);
		
		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _businessName);
	}
	
	private String createFindBusinessBasicByBusinessNameSqlScript() {
		Vector<String> selectFieldsVector =
									new Vector<String>();

		selectFieldsVector.add(_businessName);
		selectFieldsVector.add(_businessType);
		selectFieldsVector.add(ISyushinBusinessApDao._apName); // (送多方新增)
		
		// (送多方修改)
		return SqlScriptUtils.createNamedParamQueryScriptWithSingleAndGivenCondition(
								this.tableName+","+SyushinBusinessApDao.getSyushinBusinessApTalbeName(),
								selectFieldsVector,
								_businessName+"="+ISyushinBusinessApDao._businessName+"(+)",
								_businessName);
	}
	
	private String createFindAllBusinessBasicSqlScript() {
		Vector<String> selectFieldsVector =
								new Vector<String>();
		
		selectFieldsVector.add(_businessName);
		selectFieldsVector.add(_businessType);
		selectFieldsVector.add(ISyushinBusinessApDao._apName); // (送多方新增)
		
		// (送多方修改)
		return SqlScriptUtils.createNamedParamQueryScriptWithGivenCondition(
				this.tableName+","+SyushinBusinessApDao.getSyushinBusinessApTalbeName(),
				selectFieldsVector,
				_businessName+"="+ISyushinBusinessApDao._businessName+"(+)");
	}
	
	private String createDeleteByBusinessNameSqlScript() {
		return 
			SqlScriptUtils.createNamedParamDeleteScript(
					tableName, _businessName);
	}
	
	@Override
	public int updateLatestBucketStatusByBusinessName(String latestBucketStatus,
			String businessName) throws DataAccessException {
		if (null != latestBucketStatus && null != businessName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_latestBucketStatus, latestBucketStatus,
									  "__ConditionValue", businessName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateLatestBucketStatusByBusinessNameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int updateLatestBasicBucketInfoByBusinessName(int latestBucketNo,
			String latestBucketStatus, java.util.Date latestBucketTime,
			String businessName) throws DataAccessException {
		if (null != latestBucketStatus && null != latestBucketTime && null != businessName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_latestBucketNo, latestBucketNo,
									  _latestBucketStatus, latestBucketStatus,
									  _latestBucketTime, new Timestamp(latestBucketTime.getTime()),
									  "__ConditionValue", businessName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateLatestBasicBucketInfoByBusinessNameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int deleteByBusinessName(String businessName)
			throws DataAccessException {
		if (null != businessName) {
			Map<String,Object> deleteConditionValueMap
						= Collections.singletonMap(_businessName,
												   (Object)businessName);
			return
				super.namedParameterJdbcTemplate.
								update(this.deleteByBusinessNameSqlScript,
									   deleteConditionValueMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public LatestBucketInfoBean findLatestBucketInfoByBusinessName(
			String businessName) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findLatestBucketInfoByBusinessNameSqlScript,
					Collections.singletonMap((String)_businessName, (Object)businessName),
					new RowMapper<LatestBucketInfoBean>() {
						public LatestBucketInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							LatestBucketInfoBean latestBucketInfoBean = new LatestBucketInfoBean();
						
							latestBucketInfoBean.setBusinessName(rs.getString(_businessName));
							latestBucketInfoBean.setBusinessType(rs.getString(_businessType));
							latestBucketInfoBean.setLatestBucketNo(rs.getInt(_latestBucketNo));
							latestBucketInfoBean.setLatestBucketStatus(rs.getString(_latestBucketStatus));
							
							Timestamp latestBucketTimestamp = rs.getTimestamp(_latestBucketTime);
							if (null != latestBucketTimestamp) {
								latestBucketInfoBean.setLatestBucketTime(new Date(latestBucketTimestamp.getTime()));
							}
							else {
								latestBucketInfoBean.setLatestBucketTime(null);
							}
							
							return latestBucketInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	// (送多方修改)
	public BasicBusinessInfoBean findBasicBusinessInfoByBusinessName(String businessName)
													throws DataAccessException {
		try {
			SqlRowSet sqlRowSet =
						super.namedParameterJdbcTemplate.queryForRowSet(
											this.findBusinessBasicByBusinessNameSqlScript,
											Collections.singletonMap((String)_businessName, (Object)businessName));
			
			boolean hasRow = sqlRowSet.next();
			if (hasRow) {
				BasicBusinessInfoBean basicBusinessInfoBean = null;
				do {
					if (null == basicBusinessInfoBean) {
						basicBusinessInfoBean = new BasicBusinessInfoBean();
						basicBusinessInfoBean.setBusinessName(sqlRowSet.getString(_businessName));
						basicBusinessInfoBean.setBusinessType(sqlRowSet.getString(_businessType));
					}
					String apName = sqlRowSet.getString(ISyushinBusinessApDao._apName);
					if (null != apName) {
						apName = apName.trim();
						if (!apName.isEmpty()) {
							basicBusinessInfoBean.getSyushinBusinessApList().add(new ApInfoBean(apName));
						}
					}
				}while(sqlRowSet.next());
				return basicBusinessInfoBean;
			}
			else {
				return null;
			}
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	// (送多方修改)
	public List<BasicBusinessInfoBean> findAllBasicBusinessInfo()
			throws DataAccessException {
		
		JdbcOperations jdbcOperations = super.namedParameterJdbcTemplate.getJdbcOperations();
		
		Map<String, BasicBusinessInfoBean> basicBusinessInfoBeanMap = new HashMap<String, BasicBusinessInfoBean>();
		try {
			SqlRowSet sqlRowSet = jdbcOperations.queryForRowSet(this.findAllBusinessBasicSqlScript);
			
			boolean hasRow = sqlRowSet.next();
			if (hasRow) {
				do {
					String businessName = sqlRowSet.getString(_businessName);
					if (!basicBusinessInfoBeanMap.containsKey(businessName)) {
						BasicBusinessInfoBean basicBusinessInfoBean = new BasicBusinessInfoBean();
						basicBusinessInfoBean.setBusinessName(sqlRowSet.getString(_businessName));
						basicBusinessInfoBean.setBusinessType(sqlRowSet.getString(_businessType));
						
						basicBusinessInfoBeanMap.put(businessName, basicBusinessInfoBean);
					}
					String apName = sqlRowSet.getString(ISyushinBusinessApDao._apName);
					if (null != apName) {
						apName = apName.trim();
						if (!apName.isEmpty()) {
							basicBusinessInfoBeanMap.get(businessName).getSyushinBusinessApList().add(new ApInfoBean(apName));
						}
					}
				}while(sqlRowSet.next());
				return new LinkedList<BasicBusinessInfoBean>(basicBusinessInfoBeanMap.values());
			}
			else {
				return new LinkedList<BasicBusinessInfoBean>();
			}
		} catch (EmptyResultDataAccessException e) {
			return new LinkedList<BasicBusinessInfoBean>();
		}
	}

	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			return
			super.namedParameterJdbcTemplate.update(this.insertBasicSqlScript,
													insertValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public String getTableName() {
		return this.tableName;
	}
}
